package com.android.vestel.photoroid;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class DataHelper {
	public final static int PROGRAM_STATE = 0;
	public final static int SERVICE_PROVIDER = 1;
	public final static int USERNAME = 0;
	public final static int PASSWORD = 1;
	public final static int ALBUM_ID = 0;
	public final static int ALBUM_ROOT_URI = 1;
	public final static int ALBUM_URI = 2;
	public final static int PHOTO_ID = 0;
	public final static int PHOTO_URL = 1;
	public final static int IS_PHOTO_DOWNLOADED = 2;

	private static final String DATABASE_NAME = "photoroid.db";
	private static final int DATABASE_VERSION = 1;

	private static final String SETTING_TABLE_NAME = "setting";
	private static final String ACCOUNT_TABLE_NAME = "account";
	private static final String PHOTOS_TABLE_NAME = "photos";
	private static final String VAR_TABLE_NAME = "var";

	private Context context;
	private SQLiteDatabase db;

	private SQLiteStatement insertStmt1;
	private SQLiteStatement insertStmt2;
	private SQLiteStatement insertStmt3;
	private SQLiteStatement insertStmt4;

	private static final String INSERT_SETTING = "insert into "
			+ SETTING_TABLE_NAME
			+ "(program_state, service_provider) values (?, ?)";
	private static final String INSERT_ACCOUNT = "insert into "
			+ ACCOUNT_TABLE_NAME + "(username, password) values (?, ?)";
	private static final String INSERT_PHOTOS = "insert into "
			+ PHOTOS_TABLE_NAME
			+ "(photo_id, photo_url, is_downloaded) values (?, ?, ?)";
	private static final String INSERT_VAR = "insert into " + VAR_TABLE_NAME
			+ "(album_id, album_ext_uri, album_uri) values (?, ?, ?)";

	// private static final String UPDATESETTING = "UPDATE "+SETTING_TABLE_NAME
	// + "SET program_state='"

	public DataHelper(Context context) {
		this.context = context;
		OpenHelper openHelper = new OpenHelper(this.context);
		this.db = openHelper.getWritableDatabase();
		initializeDb();
	}

	public void updateAccountTable(String username, String password) {
		String UPDATEACCOUNT = "UPDATE " + ACCOUNT_TABLE_NAME
				+ " SET username='" + username + "', password='" + password
				+ "' WHERE id='1'";
		db.execSQL(UPDATEACCOUNT);
	}

	public void updateSettingTable(String program_state, String service_provider) {
		String UPDATESETTING = "UPDATE " + SETTING_TABLE_NAME
				+ " SET program_state='" + program_state
				+ "', service_provider='" + service_provider + "' WHERE id='1'";
		db.execSQL(UPDATESETTING);
	}

	public void updatePhotosTable(String photo_id, String photo_url,
			String is_downloaded) {
		String UPDATEPHOTOS = "UPDATE " + PHOTOS_TABLE_NAME + " SET photo_id='"
				+ photo_id + "', photo_url='" + photo_url
				+ "', is_downloaded='" + is_downloaded + "' WHERE photo_id='"
				+ photo_id + "'";
		db.execSQL(UPDATEPHOTOS);
	}

	public void updateVarTable(String album_id, String album_ext_uri,
			String album_uri) {
		String UPDATEVAR = "UPDATE " + VAR_TABLE_NAME + " SET album_id='"
				+ album_id + "', album_ext_uri='" + album_ext_uri
				+ "', album_uri='" + album_uri + "' WHERE id='1'";
		db.execSQL(UPDATEVAR);
	}

    public int SettingTableCount(){ 
   	 	String SETTINGCOUNT =  "SELECT Count(id) FROM "+SETTING_TABLE_NAME;
   	 	SQLiteStatement stat = db.compileStatement(SETTINGCOUNT);
   	 	long count = stat.simpleQueryForLong();
   	 	return (int) count;
    }
    
    public int AccountTableCount(){ 
   	 	String ACCOUNTCOUNT =  "SELECT Count(id) FROM "+ACCOUNT_TABLE_NAME;
   	 	SQLiteStatement stat = db.compileStatement(ACCOUNTCOUNT);
   	 	long count = stat.simpleQueryForLong();
   	 	return (int) count;
    }
    
    public int PhotosTableCount(){ 
   	 	String PHOTOSCOUNT =  "SELECT Count(id) FROM "+PHOTOS_TABLE_NAME;
   	 	SQLiteStatement stat = db.compileStatement(PHOTOSCOUNT);
   	 	long count = stat.simpleQueryForLong();
   	 	return (int) count;
    }
    
    public int VarTableCount(){ 
   	 	String VARCOUNT =  "SELECT Count(id) FROM "+VAR_TABLE_NAME;
   	 	SQLiteStatement stat = db.compileStatement(VARCOUNT);
   	 	long count = stat.simpleQueryForLong();
   	 	return (int) count;
    }
	private void initializeDb() {
		this.insertStmt1 = this.db.compileStatement(INSERT_SETTING);
		this.insertStmt2 = this.db.compileStatement(INSERT_ACCOUNT);
		this.insertStmt3 = this.db.compileStatement(INSERT_PHOTOS);
		this.insertStmt4 = this.db.compileStatement(INSERT_VAR);
	}

	public long insertToSetting(String program_state, String service_provider) {
		this.insertStmt1.bindString(1, program_state);
		this.insertStmt1.bindString(2, service_provider);
		return this.insertStmt1.executeInsert();
	}

	public long insertToAccount(String username, String password) {
		this.insertStmt2.bindString(1, username);
		this.insertStmt2.bindString(2, password);
		return this.insertStmt2.executeInsert();
	}

	public long insertToPhotos(String photo_id, String photo_url,
			String is_downloaded) {
		this.insertStmt3.bindString(1, photo_id);
		this.insertStmt3.bindString(2, photo_url);
		this.insertStmt3.bindString(3, is_downloaded);
		return this.insertStmt3.executeInsert();
	}

	public long insertToVar(String album_id, String album_ext_uri,
			String album_uri) {
		this.insertStmt4.bindString(1, album_id);
		this.insertStmt4.bindString(2, album_ext_uri);
		this.insertStmt4.bindString(2, album_uri);
		return this.insertStmt4.executeInsert();
	}

	public void deleteAll() {
		this.db.delete(ACCOUNT_TABLE_NAME, null, null);
		this.db.delete(SETTING_TABLE_NAME, null, null);
		this.db.delete(PHOTOS_TABLE_NAME, null, null);
		this.db.delete(VAR_TABLE_NAME, null, null);
	}

	public List<String> selectAllOfSetting() {
		List<String> list = new ArrayList<String>();
		Cursor cursor = this.db.query(SETTING_TABLE_NAME, new String[] {
				"program_state", "service_provider" }, null, null, null, null,
				"program_state desc");
		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getString(0));
				list.add(cursor.getString(1));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}

	public List<String> selectAllOfAccounts() {
		List<String> list = new ArrayList<String>();
		Cursor cursor = this.db.query(ACCOUNT_TABLE_NAME, new String[] {
				"username", "password" }, null, null, null, null,
				"username desc");
		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getString(0));
				list.add(cursor.getString(1));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}

	public List<String> getAllPhotoIDs() {
		List<String> list = new ArrayList<String>();
		Cursor cursor = this.db.query(PHOTOS_TABLE_NAME, new String[] {
				"photo_id" }, null, null, null,
				null, "photo_id asc");
		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getString(0));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}
	public List<String> getAllPhotoURLs() {
		List<String> list = new ArrayList<String>();
		Cursor cursor = this.db.query(PHOTOS_TABLE_NAME, new String[] {
				"photo_id", "photo_url", "is_downloaded" }, null, null, null,
				null, "photo_id desc");
		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getString(1));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}
	public ArrayList<String> getPhotosNotDownloaded() {
		ArrayList<String> list = new ArrayList<String>();
		Cursor cursor = this.db.query(PHOTOS_TABLE_NAME, new String[] {
				"photo_id",  }, "is_downloaded=0", null, null,
				null, "photo_id desc");
		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getString(0));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}
	public String removeItemFromPhotos(String AlbumID){
		String[] strarr = new String[]{ AlbumID };
		if (db.delete(PHOTOS_TABLE_NAME, "photo_id=?",strarr) <= 0){
			Log.e("DataHelper", "Not Deleted AlbumID="+AlbumID);
		}
		
		return AlbumID;
	}
	public List<String> selectAllOfVar() {
		List<String> list = new ArrayList<String>();
		Cursor cursor = this.db.query(VAR_TABLE_NAME, new String[] {
				"album_id", "album_ext_uri", "album_uri" }, null, null, null,
				null, "album_id desc");
		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getString(0));
				list.add(cursor.getString(1));
				list.add(cursor.getString(2));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}

	public List<String> selectAllOfPhotos() {
		List<String> list = new ArrayList<String>();
		Cursor cursor = this.db.query(PHOTOS_TABLE_NAME, new String[] {
				"photo_id", "photo_url", "is_downloaded" }, null, null, null,
				null, "photo_id desc");
		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getString(0));
				list.add(cursor.getString(1));
				list.add(cursor.getString(2));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}
	public int deleteAllPhotos(){
		return db.delete(PHOTOS_TABLE_NAME, null, null);
		
	}
	public int deleteFromPhotosTable(String photoID){
		return db.delete(PHOTOS_TABLE_NAME, "photo_id=?", new String[] {photoID});
		
	}
	public void closeDB(){
		db.close();
	}

	private static class OpenHelper extends SQLiteOpenHelper {

		OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("CREATE TABLE "
					+ ACCOUNT_TABLE_NAME
					+ " "
					+ "(id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT , password TEXT)");
			db.execSQL("CREATE TABLE "
					+ SETTING_TABLE_NAME
					+ " "
					+ "(id INTEGER PRIMARY KEY AUTOINCREMENT, program_state TEXT, service_provider TEXT)");
			db.execSQL("CREATE TABLE "
					+ PHOTOS_TABLE_NAME
					+ " "
					+ "(id INTEGER PRIMARY KEY AUTOINCREMENT, photo_id TEXT , photo_url TEXT , is_downloaded TEXT)");
			db.execSQL("CREATE TABLE "
					+ VAR_TABLE_NAME
					+ " "
					+ "(id INTEGER PRIMARY KEY AUTOINCREMENT, album_id TEXT , album_ext_uri TEXT , album_uri TEXT)");

			db.execSQL("INSERT INTO " + SETTING_TABLE_NAME
					+ " (program_state, service_provider) VALUES ('0','null')");
			db.execSQL("INSERT INTO " + ACCOUNT_TABLE_NAME
					+ " (username, password) VALUES ('null','null')");
			db.execSQL("INSERT INTO "
					+ VAR_TABLE_NAME
					+ " (album_id, album_ext_uri, album_uri) VALUES ('null','null','null')");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w("Example",
					"Upgrading database, this will drop tables and recreate.");
			db.execSQL("DROP TABLE IF EXISTS " + ACCOUNT_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + SETTING_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + PHOTOS_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + VAR_TABLE_NAME);
			onCreate(db);
		}
	}
}
